import pandas as pd
import xlwings as xw
import numpy as np


def is_range_address(address):
    try:
        rng = xw.Range(address)
    except Exception:
        return False
    return True


def is_excel_exist():
    try:
        sheet = xw.sheets.active
    except Exception:
        return False
    return True


def get_unique_sheet_name(book, name):
    name_list = book.sheet_names
    while name in name_list:
        name += '_'
    return name


class SummaryTool:
    """
    数据汇总工具类
    用于对Excel中的数据进行分组汇总
    """

    def __init__(self):
        self.data_range = None
        self.condition_labels = []
        self.summary_labels = []
        self.index_column = None
        self.delete_zero = False

    def set_params(self, ui_data):
        self.data_range = ui_data.get('data_range')
        self.condition_labels = ui_data.get('condition_labels').replace('，', ',').split(',')
        self.summary_labels = ui_data.get('summary_labels').replace('，', ',').split(',')
        self.index_column = ui_data.get('index_column')
        self.delete_zero = ui_data.get('delete_zero', False)

    def get_data_index(self, label_list):
        """
        将列区域列表转为相对于DataFrame的列索引（从0开始）

        :param label_list: 列区域列表
        :return: 列索引列表（相对于DataFrame）
        """
        index_list = []
        # 获取data_range的起始列
        data_range_start_col = self._get_range_start_col(self.data_range)

        for address in label_list:
            # 处理绝对引用符号如 $C$2
            address = address.replace('$', '')
            # 处理区域，如 C2:D2
            if ':' in address:
                # 区域格式，如 C2:D2
                start_cell, end_cell = address.split(':')
                start_col = ''.join(filter(str.isalpha, start_cell))
                end_col = ''.join(filter(str.isalpha, end_cell))

                # 将列字母转换为索引
                start_col_index = self._col_letter_to_index(start_col)
                end_col_index = self._col_letter_to_index(end_col)
                data_range_start_index = self._col_letter_to_index(data_range_start_col)

                # 添加范围内的所有列索引（相对于data_range）
                for i in range(start_col_index, end_col_index + 1):
                    index_list.append(i - data_range_start_index)
            else:
                # 单个单元格格式，如 C2
                col = ''.join(filter(str.isalpha, address))
                col_index = self._col_letter_to_index(col)
                data_range_start_index = self._col_letter_to_index(data_range_start_col)
                index_list.append(col_index - data_range_start_index)

        return index_list

    @staticmethod
    def _get_range_start_col(range_address):
        """
        获取区域地址的起始列字母

        :param range_address: 区域地址，如 A1:D10
        :return: 起始列字母，如 A
        """
        # 移除绝对引用符号
        range_address = range_address.replace('$', '')
        # 获取起始单元格
        if ':' in range_address:
            start_cell = range_address.split(':')[0]
        else:
            start_cell = range_address

        # 提取列字母
        col_letter = ''.join(filter(str.isalpha, start_cell))
        return col_letter

    @staticmethod
    def _col_letter_to_index(col_letter):
        """
        将Excel列字母转换为索引（从0开始）
        例如: A->0, B->1, ..., Z->25, AA->26, AB->27, ...

        :param col_letter: 列字母
        :return: 列索引
        """
        result = 0
        for char in col_letter:
            result = result * 26 + (ord(char.upper()) - ord('A') + 1)
        return result - 1

    def read_data(self, sheet):
        """
        从Excel读取数据

        :param sheet: Excel工作表对象
        :return: DataFrame数据
        """
        # 读取数据区域（包含标题行）
        all_data = sheet.range(self.data_range).value
        # 分离标题行和数据行
        if isinstance(all_data[0], list):
            # 多行数据
            header = all_data[0]
            data = all_data[1:]
        else:
            # 只有一行数据
            header = all_data
            data = []
        # 检查标题行
        # 1. 检查是否有缺失项
        if any(h is None or str(h).strip() == '' for h in header):
            return False
        # 2. 检查是否有重复项
        if len(header) != len(set(str(h).strip() for h in header)):
            return False
        # 3. 检查是否与index_column重复
        if self.index_column:
            header_str = [str(h).strip() for h in header]
            while self.index_column in header_str:
                self.index_column = '_' + self.index_column
        # 创建DataFrame
        df = pd.DataFrame(data, columns=header)
        return df

    def summary(self, sheet_name, data):
        """
        执行数据汇总

        :param sheet: Excel工作表对象
        :return: 汇总结果表格（包含标题行）
        """
        try:
            sheet = xw.sheets[sheet_name]
            self.set_params(data)
            # 读取数据
            data = self.read_data(sheet)
            # 如果read_data返回False，说明数据有问题
            if data is False:
                return f'读取{self.data_range}数据时出现错误，请检查首行的列名是否有重复或缺失值'
            # 获取条件列和汇总列的索引
            condition_cols = self.get_data_index(self.condition_labels)
            summary_cols = self.get_data_index(self.summary_labels)
            # 获取列名
            group_col_names = [data.columns[i] for i in condition_cols]
            sum_col_names = [data.columns[i] for i in summary_cols]
            # 转换汇总列为数值型（无法转换的视为0）
            for col in sum_col_names:
                data[col] = pd.to_numeric(data[col], errors='coerce').fillna(0)
            # 按条件列分组并求和
            if group_col_names:
                result = data.groupby(group_col_names)[sum_col_names].sum().reset_index()
            else:
                # 如果没有分组列，直接对所有汇总列求和
                sums = data[sum_col_names].sum()
                result = pd.DataFrame([sums], columns=sum_col_names)

            # 如果delete_zero为True，删除所有summary_labels都为0的行
            if self.delete_zero:
                # 检查是否所有汇总列都为0
                if sum_col_names:
                    # 创建条件：所有汇总列都等于0
                    condition = (result[sum_col_names] == 0).all(axis=1)
                    # 删除满足条件的行
                    result = result[~condition]
            result = result.reset_index(drop=True)
            if self.index_column:
                result.insert(0, self.index_column, pd.Series(range(1, len(result) + 1)))
            # 转换为二维列表格式（包含标题）
            result_table = [result.columns.tolist()] + result.values.tolist()
            return result_table
        except Exception as e:
            return f'执行数据汇总时出现错误：{str(e)}'

    @staticmethod
    def write_result(result_table, sheet, start_cell='A1'):
        """
        将汇总结果写入Excel工作表

        :param result_table: 汇总结果表格
        :param sheet: 当前工作表
        :param start_cell: 起始单元格，默认'A1'
        """
        book = sheet.book
        sheet_name = sheet.name
        unique_sheet_name = get_unique_sheet_name(book, f'【{sheet_name}】汇总结果')
        target_sheet = book.sheets.add(unique_sheet_name, after=sheet)
        target_sheet.range(start_cell).value = result_table
